import xlrd
import pymysql

connect = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='123456',
    db='shop',
    charset='utf8'
)


def deleteRepe():
    # 删除重复元素

    rbook = xlrd.open_workbook(r'C:\Users\da\Desktop\1.xls')
    rbook.sheets()
    rsheet = rbook.sheet_by_index(0)

    cursor = connect.cursor()
    for row in rsheet.get_rows():
        sql = "select attr_value from ecs_goods_attr where goods_id='" + row[0].value + "'"
        cursor.execute(sql)
        goods = cursor.fetchall()
        if len(goods) != 0:
            if len(set(goods)) != len(goods):
                print('' + str(row[0].value) + '__列表里有重复的元素！')


def changePrice():
    # 修改售价

    rbook2 = xlrd.open_workbook(r'C:\Users\da\Desktop\1.xls')
    rbook2.sheets()
    rsheet2 = rbook2.sheet_by_index(0)

    cursor = connect.cursor()
    for row in rsheet2.get_rows():
        if row[0].value != '商品名称':
            if row[0].value == '':
                print('结束')
                break

            show = "select goods_id from ecs_goods where goods_name='" + row[0].value + "'"
            cursor.execute(show)
            allAttr = cursor.fetchall()
            if len(allAttr) != 0:
                change = "update ecs_goods set shop_price='" + str(int(row[14].value)) + "',market_price='" + str(
                    int(row[15].value)) + "' where goods_id='" + str(allAttr[0][0]) + "'"
                cursor.execute(change)


def changeGuige():
    # 修改规格售价

    rbook = xlrd.open_workbook(r'C:\Users\da\Desktop\1.xls')
    rbook.sheets()
    rsheet = rbook.sheet_by_index(0)
    cursor = connect.cursor()

    for row in rsheet.get_rows():
        if row[0].value != '商品名称':
            show = "select goods_id from ecs_goods where goods_name='" + row[0].value + "'"
            cursor.execute(show)
            goodsId = cursor.fetchall()

            if len(goodsId) != 0:
                sql2 = "select goods_attr_id,attr_value from ecs_goods_attr where goods_id='" + str(goodsId[0][0]) + "'"
                cursor.execute(sql2)
                allAttr = cursor.fetchall()

                if len(allAttr) != 0:  # 规格不为空
                    if row[8].value != '':
                        for goodsAttr in allAttr:
                            if goodsAttr[1] == row[8].value:
                                price=str(int(row[9].value)-int(row[14].value))
                                update = "UPDATE ecs_goods_attr SET attr_price='" + price + "' WHERE goods_attr_id='" + str(goodsAttr[0]) + "'"
                                cursor.execute(update)

                    if row[10].value != '':
                        for goodsAttr in allAttr:
                            if goodsAttr[1] == row[10].value:
                                price = str(int(row[11].value) - int(row[14].value))
                                update = "UPDATE ecs_goods_attr SET attr_price='" + price + "' WHERE goods_attr_id='" + str(goodsAttr[0]) + "'"
                                cursor.execute(update)

                    if row[12].value != '':
                        for goodsAttr in allAttr:
                            if goodsAttr[1] == row[12].value:
                                price = str(int(row[13].value) - int(row[14].value))
                                update = "UPDATE ecs_goods_attr SET attr_price='" + price + "' WHERE goods_attr_id='" + str(goodsAttr[0]) + "'"
                                cursor.execute(update)
                    print("-----------------------------------------------------")

                    # if len(allAttr) != 0:
                    #     print(row[0].value)
                    #
                    #     for attr in allAttr:
                    #
                    #         print(attr[0])
                    #     print("-----------------------------------------------------")


if __name__ == '__main__':
    changeGuige()
